package DB;

import Model.Session;
import Model.Student;
import Model.Teacher;

import java.sql.*;
import java.util.ArrayList;

public class SessionDB implements SessionDBIF{
    private Connection conn;


    public SessionDB() {
        conn = ConnectionDB.getInstance().getConn();

    }

    @Override
    public int insertSession(Session session) {
        int rc = -1;
        PreparedStatement pstmt;

        String query="INSERT INTO session(sessionId, date,teacherId,teamId,courseId)  VALUES(?,?,?,?,?)";
        System.out.println("insert : " + query);
        try{
            pstmt = conn.prepareStatement(query);
            pstmt.setQueryTimeout(5);
            pstmt.setInt(1,session.getId());
            pstmt.setString(2,session.getDate());
            pstmt.setInt(3,session.getTeacherId().getId());
            pstmt.setInt(4,session.getTeamId().getId());
            pstmt.setInt(5,session.getCourseId().getId());
            rc = pstmt.executeUpdate();
            pstmt.close();
        }
        catch(SQLException ex){
            System.out.println("SessionNo Insert Error" + ex.getMessage());
        }
        return(rc);
    }

    @Override
    public Session findSession(int id) {
        return singleWhere("sessionId = " + id, false);
    }

    @Override
    public Session findSessionByStudentIdAndDate(Student student, String date) {
        return singleWhere("teamId = " + student.getTeamId().getId() + " AND date = '" + date + "'", false);
    }

    @Override
    public Session getSessionByTeacherAndDate(Teacher teacher, String date) {
        return singleWhere("teacherId = " + teacher.getId() + " AND date = '" + date + "'", false);
    }

    @Override
    public ArrayList<Session> getAllSessions(boolean retrieveAssociation) {
        return miscWhere("", false);
    }

    @Override
    public ArrayList<Session> getAllSessionsBetweenDates(String startDate, String endDate, int teamId, boolean retrieveAssociation) {
        return miscWhere(" date BETWEEN '" + startDate + "' AND '" + endDate + "' AND teamId = " + teamId, false);
    }

    @Override
    public ArrayList<Session> getAllTeacherSessions(Teacher teacher, boolean reteiveAssociation) {
        return miscWhere(" teacherId = " + teacher.getId(), false);
    }

    @Override
    public int updateSession(Session session) {

        Session seObj = session;
        int rc = -1;

        String query="UPDATE Session SET " +
                "date = '" +seObj.getDate() + "',"+
                "teacherID = '" +seObj.getTeacherId().getId() + "',"+
                "teamId = '" +seObj.getTeamId().getId()+ "' ,"+
                "courseId = '" +seObj.getCourseId().getId() + "'"+
                " WHERE sessionId = " + seObj.getId() + "";
        System.out.println("Update query: " + query);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch(Exception e){
            System.out.println("Update exception in session db: " +e);
        }
        return rc;
    }

    @Override
    public int deleteSession(int id) {
        int rc = -1;

        String query = "DELETE FROM Session WHERE sessionId = '" + id + "'";

        System.out.println(query);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch(Exception e){
            System.out.println("Delete exception in session db: " + e);
        }
        return(rc);
    }

    //Finding a single session
    private Session singleWhere(String wClause, boolean retrieveAssociation) {
        ResultSet results;
        Session supObj = new Session();

        String query = buildQuery(wClause);
        System.out.println(query);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if (results.next()) {
                supObj = buildSession(results);
                stmt.close();
            }
            else {
                supObj = null;
            }
        }
        catch (Exception e) {
            System.out.println("Query exception: " + e);
        }

        return supObj;
    }

    //Finding all sessions
    private ArrayList<Session> miscWhere(String wClause, boolean retrieveAssociation) {
        ResultSet results;
        ArrayList<Session> list = new ArrayList<Session>();

        String query = buildQuery(wClause);

        try {
            Statement stmt = conn.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while (results.next()) {
                Session seObj = new Session();
                seObj = buildSession(results);
                list.add(seObj);
            }

            stmt.close();
        }
        catch (Exception e) {
            System.out.println("Query exception: " + e);
            e.printStackTrace();
        }

        return list;
    }

    //Method to build the query
    private String buildQuery(String wClause) {
        String query = "SELECT sessionId, date, teacherId, teamId, courseId FROM Session";

        if (wClause.length()>0) {
            query = query + " WHERE " + wClause;
        }

        return query;
    }

    //Method to build a session object
    private Session buildSession(ResultSet results) {

        Session seObj = new Session();
        TeacherDB teacherDB = new TeacherDB();
        TeamDB teamDB = new TeamDB();
        CourseDB courseDB = new CourseDB();


        try {
            seObj.setId(results.getInt("sessionId"));
            seObj.setDate(results.getString("date"));
            seObj.setTeacherId(teacherDB.findTeacherByID(results.getInt("teacherId")));
            seObj.setTeamId(teamDB.findTeam(results.getInt("teamId")));
            seObj.setCourseId(courseDB.findCourse(results.getInt("courseId")));
        }
        catch (Exception e) {
            System.out.println("Error building session object");
        }

        return seObj;
    }

    public int sessionGetMax() {
        int nextId = GetMax.getMaxId("SELECT max(sessionId) from Session");
        nextId = nextId +1;
        return nextId;
    }

}
